package nol_app;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

/**
 * @author Sirius
 *         Additional class implementations by Bryan Farlow
 *         3/16/2011
 * 
 */
public class DatabaseAPI extends MysqlConnection {

	private static double daily_fine = 0.75;

	/**
	 * Searches the catalog for book that match ALL of the given criteria
	 * 
	 * @param String title any title keywords, may be empty string
	 * @param String author_name author partial or full name in the form
	 *        "first_name last_name", may be empty string
	 * @return ArrayList<Book> an array list of book objects, each book object
	 *         represents a book returned by the search
	 */
	public ArrayList<Book> Search(String title, String author_name) {
		ArrayList<Book> books = new ArrayList<Book>();
		////		PreparedStatement stmt = null;
		//		String sql = null;
		//		int rows_updated = 0;
		//		ResultSet result;
		//		String the_isbn;
		//		String the_title;
		//		String the_authors;
		//		String the_year;

		// need isbn, title, author(s), year
		//		sql = "SELECT isbn FROM book WHERE concat(first_name, ' ', last_name) " +
		//				"LIKE (%first_name%last_name%)";

		//		PreparedStatement select_stmt = this.conn.prepareStatement(sql);
		//		select_stmt.setString(1, authors[x][0]);
		//		select_stmt.setString(2, authors[x][1]);
		//		result = select_stmt.executeQuery();

		return books;
	}

	/**
	 * Reserves a book for a user if that book is available
	 * 
	 * @param int inventory_id the inventory id of the book to be reserved
	 * @param int user_id the user id of the reserver
	 * @return boolean true on successful reserve, false if the book wasn't in
	 *         circulation when the reserve was attempted
	 */
	public boolean ReserveBook(int inventory_id, int user_id) {
		PreparedStatement stmt = null;
		String sql = null;
		int rows_updated = 0;

		// Set the reserve expiration date 1 week from today
		String reserve_expiration;
		Calendar now = Calendar.getInstance();
		now.add(Calendar.WEEK_OF_YEAR, 1);
		reserve_expiration = now.get(Calendar.YEAR) + "-"
				+ (now.get(Calendar.MONTH) + 1) // 0-based indexing
				+ "-" + now.get(Calendar.DATE);

		try {
			// Update the inventory by setting the item status to reserved by 
			// a particular user.
			sql = "UPDATE inventory SET status = 'reserved', user_id = ?, "
					+ "reserve_expiration = ? WHERE status = 'in' AND id = ?";
			stmt = this.conn.prepareStatement(sql);
			stmt.setInt(1, user_id);
			stmt.setString(2, reserve_expiration);
			stmt.setInt(3, inventory_id);

			// Get number of rows updated in the query
			// should be 1 for successful checkout
			rows_updated = stmt.executeUpdate();

			// Return resources for garbage collection
			stmt.close();

			// Commit this transaction
			this.conn.commit();

		} catch (SQLException ex) {
			handleException(ex);
		}

		// Return true if the UPDATE actually updated a row
		return (rows_updated == 1);
	}

	/**
	 * Attempts to checkout a book
	 * 
	 * @param int inventory_id the inventory id of the book to checkout
	 * @param int user_id the user id of the user to check the book out to
	 * @return boolean true if checkout was successful, false if book was taken
	 *         out of circulation during the checkout process
	 */
	public boolean CheckoutBook(int inventory_id, int user_id) {
		PreparedStatement stmt = null;
		String sql = null;
		int rows_updated = 0;

		// Set the due date to 3 weeks from today
		String due_date;
		Calendar now = Calendar.getInstance();
		now.add(Calendar.WEEK_OF_YEAR, 3);
		due_date = now.get(Calendar.YEAR) + "-" + (now.get(Calendar.MONTH) + 1)
				+ "-" + now.get(Calendar.DATE);

		try {
			// Update inventory, set status to 'out' for a particular user,
			// but only if the status is currently set to "in" (i.e. no changes
			// have occurred since reserve process began)
			sql = "UPDATE inventory SET status = 'out', user_id = ?, "
					+ "due_date = ? WHERE status = 'in' AND id = ?";
			stmt = this.conn.prepareStatement(sql);
			stmt.setInt(1, user_id);
			stmt.setString(2, due_date);
			stmt.setInt(3, inventory_id);

			// Get number of rows updated in the query
			// should be 1 for successful checkout
			rows_updated = stmt.executeUpdate();

			// Return resources for garbage collection
			stmt.close();

			// Commit this transaction
			this.conn.commit();

		} catch (SQLException ex) {
			handleException(ex);
		}

		// Return true if the UPDATE actually updated a row
		return (rows_updated == 1);
	}

	/**
	 * Check a book back in, applying any accrued late fees to the user who
	 * had the book
	 * 
	 * @param int inventory_id the inventory id of the book that was checked in
	 */
	//	@SuppressWarnings("null")
	public void CheckinBook(int inventory_id) {
		// Get due_date of inventory_id; if today is > due date, check # of days
		// difference.  If difference > 1 day, set 'user'.'acct_balance' to 
		// ('daily_fine' * # of days difference
		String sql = null;
		int rows_updated = 0;

		// Create two calendar objects to compare
		Calendar due_date = Calendar.getInstance();
		Calendar return_date = Calendar.getInstance();
		try {
			ResultSet query_result;
			sql = "SELECT acct_balance FROM user JOIN inventory "
					+ "WHERE user.id = inventory.user_id AND inventory.id = ?";
			PreparedStatement stmt = this.conn.prepareStatement(sql);
			stmt.setInt(1, inventory_id);
			// Get the date from the database query 
			query_result = stmt.executeQuery();
			query_result.next();
			double acct_balance = query_result.getDouble(1);
			query_result.close();
			stmt.close();

			// Update inventory, set status to 'out' for a particular user,
			// but only if the status is currently set to "in" (i.e. no changes
			// have occurred since reserve process began)
			sql = "SELECT * FROM inventory WHERE id = ?";
			stmt = this.conn.prepareStatement(sql);
			stmt.setInt(1, inventory_id);
			// Get the date from the database query 
			query_result = stmt.executeQuery();
			query_result.next();
			Date db_date = query_result.getDate("due_date");
			query_result.close();
			stmt.close();

			// Use the above date to set the calendar obj's date
			due_date.setTime((Date) db_date);

			// Check if due_date is before return_date
			if (due_date.before(return_date)) {
				long daysDiff = (return_date.getTimeInMillis() - due_date
						.getTimeInMillis()) / 86400000;
				if (daysDiff > 1) {
					sql = "UPDATE user SET acct_balance = ? WHERE id = ?";
					stmt = this.conn.prepareStatement(sql);
					stmt.setDouble(1, acct_balance + (daily_fine * daysDiff));
					//					stmt.setString(2, "NULL");
					stmt.setInt(2, inventory_id);
					stmt.executeUpdate();
					stmt.close();
				}
			}

			sql = "UPDATE inventory SET status = 'in', user_id = NULL, "
					+ "due_date = NULL WHERE id = ?";
			stmt = this.conn.prepareStatement(sql);
			//			stmt.setString(1, "");
			//			stmt.setString(2, "");
			stmt.setInt(1, inventory_id);

			// Get number of rows updated in the query
			// should be 1 for successful checkout
			rows_updated = stmt.executeUpdate();
			if (rows_updated != 1)
				System.out.println("This book wasn't found.  No changes were made.");
			stmt.close();

			// Commit this transaction
			this.conn.commit();

		} catch (SQLException ex) {
			handleException(ex);
		}
	}

	/**
	 * Add a new book to the database
	 * 
	 * @param String isbn
	 * @param String title
	 * @param int year
	 * @param String[][] authors see description in AddAuthors()
	 * @param int copies the number of copies the library has of this book
	 */
	public void AddBook(String isbn, String title, int year,
			String[][] authors, int copies) {
		// Add book, then pass authors to AddAuthors() method.
		String sql = null;
		ResultSet result = null;

		// Create the book, then one 'inventory' entry for each copy of the book
		try {
			// See if this book's ISBN already exists in the database
			sql = "SELECT * FROM book WHERE isbn = ?";

			PreparedStatement select_stmt = this.conn.prepareStatement(sql);
			select_stmt.setString(1, isbn);
			result = select_stmt.executeQuery();

			// If the result set isn't null, the isbn already exists in the database.
			if (result.next()) {
				System.out
						.println("ISBN "
								+ isbn
								+ " already exists in the database.  No changes were made.");
				//				result.close(); // let go of the resultset
			} else {
				//				result.close(); // let go of the resultset
				System.out.println(isbn + " " + title + " " + year);
				sql = "INSERT INTO book (isbn, title, year) VALUES (?, ?, ?)";
				PreparedStatement insert_stmt = this.conn.prepareStatement(sql);
				insert_stmt.setString(1, isbn);
				insert_stmt.setString(2, title);
				insert_stmt.setString(3, year + "-1-1");
				insert_stmt.executeUpdate();
				insert_stmt.close();

				// Add one entry to 'inventory' for each copy
				for (int i = 0; i < copies; i++) {
					sql = "INSERT INTO inventory (isbn, status) VALUES (?, ?)";
					insert_stmt = this.conn.prepareStatement(sql);
					insert_stmt.setString(1, isbn);
					insert_stmt.setString(2, "in");
					insert_stmt.executeUpdate();
				}
				insert_stmt.close();

				// Create associations with all of the authors of the book
				AddAuthors(isbn, authors);
			}

			// Commit this transaction
			this.conn.commit();

		} catch (SQLException ex) {
			handleException(ex);
		}

	}

	/**
	 * Called by AddBook to add all authors associated with a book
	 * 
	 * @param String isbn the isbn of the book the author should be added to
	 * @param String[][] authors a 2D array of author first and last names where
	 *        authors[0][0] is the first author's first name
	 *        and authors[0][1] is the first author's last name
	 */
	private void AddAuthors(String isbn, String[][] authors) {
		String sql = null;
		ResultSet result = null;
		int author_id = -1;

		try {

			// Loop through the authors array
			for (int x = 0; x < authors.length; x++) {

				// See if this author already exists in the database
				sql = "SELECT * FROM author WHERE first_name = ? AND last_name = ?";

				PreparedStatement select_stmt = this.conn.prepareStatement(sql);
				select_stmt.setString(1, authors[x][0]);
				select_stmt.setString(2, authors[x][1]);
				result = select_stmt.executeQuery();

				// If the author already exists, get their id
				if (result.next()) {
					author_id = result.getInt("id");

					// Otherwise, insert the author and retrieve the insert id
				} else {
					sql = "INSERT INTO author VALUES (null, ?, ?)";
					PreparedStatement insert_stmt = this.conn
							.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					insert_stmt.setString(1, authors[x][0]);
					insert_stmt.setString(2, authors[x][1]);
					insert_stmt.executeUpdate();

					ResultSet key_result = insert_stmt.getGeneratedKeys();

					if (key_result.next()) {
						author_id = key_result.getInt(1);
					}
					key_result.close();
					insert_stmt.close();
				}
				result.close();
				select_stmt.close();

				// Insert the book/author into the interaction table
				sql = "INSERT INTO book_author VALUES (?, ?)";
				PreparedStatement insert_stmt = this.conn.prepareStatement(sql);
				insert_stmt.setString(1, isbn);
				insert_stmt.setInt(2, author_id);
				insert_stmt.executeUpdate();

				insert_stmt.close();
			}

			// Commit this transaction
			this.conn.commit();

		} catch (SQLException ex) {
			handleException(ex);
		}
	}

	/**
	 * Add a user (patron) to the database
	 * 
	 * @param String first_name
	 * @param String last_name
	 * @param String email
	 * @param String address
	 * @param String city
	 * @param String state
	 * @param String zip
	 */
	public void AddUser(String first_name, String last_name, String email,
			String address, String city, String state, String zip) {

		// Initialize statement / sql string variables
		//		PreparedStatement stmt = null;
		String sql = null;

		// Attempt to run the sql query(s)
		try {

			// Insert the book
			sql = "INSERT INTO user "
					+ "(first_name, last_name, email, address, city, state, zip)"
					+ " VALUES (?, ?, ?, ?, ?, ?, ?)";

			// Create the statement from this connection
			// and run the sql
			PreparedStatement stmt = this.conn.prepareStatement(sql);
			stmt.setString(1, first_name);
			stmt.setString(2, last_name);
			stmt.setString(3, email);
			stmt.setString(4, address);
			stmt.setString(5, city);
			stmt.setString(6, state);
			stmt.setString(7, zip);
			stmt.executeUpdate();

			// Close the statement resource
			stmt.close();

			// Commit this transaction
			this.conn.commit();

			// Catch any sql exceptions
		} catch (SQLException ex) {
			handleException(ex);
		}
	}
}